package com.zhang.crawler.crawler.yicheng;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.zhang.crawler.crawler.utils.StringUtils;
import com.zhang.crawler.crawler.utils.Tools;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author zhanghaidong
 * @version v1.0
 * @description
 * @date 2019/5/28
 */
public class PatentSql {

    private static JSONArray json = new JSONArray();

    static {
        try {
            InputStream in = PatentSql.class.getClassLoader().getResourceAsStream("patent5281.json");
            json = JSON.parseArray(StringUtils.inputStream2String(in));
            System.out.println("---------------json加载成功-------------------\n" + json.toJSONString());
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("-----------------------------error---------------------");
        }
    }

    public static void main(String[] args) {

        news1();
    }

    public static Connection getConnection() {
        String driver="com.mysql.jdbc.Driver";
        String url="jdbc:mysql://192.168.30.188:3306/wtoip_yun_yicheng?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8";
        String user="root";
        String password="wtoip@2017";
        try {
            //1、加载驱动
            Class.forName(driver);
            //2、链接数据库
            return DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }

    public static  void news1(){

        Statement statement = null;

        /**
         CREATE TABLE `t_yc_patent` (
         `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
         `name` varchar(256) NOT NULL COMMENT '专利名称',
         `image_url` varchar(128) NOT NULL COMMENT '专利图片',
         `patent_type` int(20) NOT NULL COMMENT '专利类型',
         `belong_org` int(20) NOT NULL COMMENT '所属机构',
         `province` varchar(128) NOT NULL COMMENT '省份代码',
         `patent_field` int(20) NOT NULL COMMENT '专利领域',
         `patent_detail` varchar(1000) NOT NULL COMMENT '专利详情',
         `hot_flag` char(1) DEFAULT NULL COMMENT '热门标记',
         `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
         `update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
         PRIMARY KEY (`id`)
         ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='知识产权表';


         */
        try {
            Connection con = getConnection();

            String sql = "insert into t_yc_patent" +
                    " (`name`, `image_url`, " +

                    "`patent_type`, `belong_org`, " +

                    "`province`, `patent_field`, " +

                    "`patent_detail`, `hot_flag`) " +

                    "values (";

            /**执行SQL预编译**/
            statement = con.createStatement();

            for (int i = 0; i < json.size() ; i++) {
                JSONObject jsonObject = json.getJSONObject(i);
                String transAskPerson = jsonObject.getString("transAskPerson");
                String province = jsonObject.getString("province");
                String imageUrl = jsonObject.getString("imageUrl");
                String name = jsonObject.getString("name");
                String technicalField = jsonObject.getString("technicalField");
                String description = jsonObject.getString("description");
                String transformType = jsonObject.getString("transformType");

                StringBuilder builder = new StringBuilder();
                builder.append(sql).append("'").append(name).append("',").
                        append("'").append(imageUrl).append("',").
                        append("'").append(Tools.value2code(transformType, Tools.PATENT_TYPE)).append("',").
                        append("'").append(Tools.value2code(transAskPerson, Tools.PATENT_AFFILIATION)).append("',").
                        append("'").append(province).append("',").
                        append("'").append(Tools.value2code(technicalField, Tools.PATENT_FIELD)).append("',").
                        append("'").append(description).append("',").
                        append("'").append("1").append("'").append(")");


                System.out.println(builder.toString());
                statement.executeUpdate(builder.toString());

            }


            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
